Answer: It's natural to wonder - "dissonant" is commonly thought to mean "unpleasant," yet the fourth sounds pretty good.

The answer lies in understanding "dissonance" to mean something more akin to "unstable" rather than "unpleasant."

After all, even the Tritone (augmented fourth), reputedly the worst of all intervals, actually is kind of pretty (think of Bernstein's "Maria," from West Side Story). But it is considered the most unstable of intervals as a harmony and difficult to sing as melody, because acoustically its pitches have nothing in common; there is no reinforcement between them.

The appendix of Exploring Theory with Practica Musica has a more thorough discussion of musical acoustics, but the basic principle is that musical tones are complex and composed of numerous pitches in combination, whose frequencies are close to being integer multiples of the fundamental frequency created by the full column of air or the full length of the sounding string. These "harmonics" grow increasingly faint as they rise, but the first six all repeat the pitches of what we know as the major triad based on the fundamental. When you sound two complex pitches together, one or more of the harmonics in each may coincide, which leads to the possibility of a wavering "beating" effect if the two pitches are not tuned to an exact proportion. Combinations that have the potential to reinforce each other in this way are the octave, fifth, fourth, major third, and minor third, in descending order of strength. Theoretically there could be more, but in practice these agreements become increasingly hard to detect.

This is a bit controversial: you'll find people who will object that the interactions between these partials cannot be so significant since we rarely hear music performed in which the fundamentals are tuned precisely this way, plus which the effects are fleeting, etc. Yet these numeric relationships between sounding pitches have informed music theory since its origins. If you take the time to listen to these interactions you'll find they affect your perception, and they do provide a logical explanation for tonality in music. Some (like me) think that our perception allows for imprecision in these relationships, just as it fills in missing detail in vision. The tones of a piano are all slightly "off" acoustically, but the strongest intervals, fifths and fourths, are so near to exact that one can easily hear the beating, and the thirds are fairly close, too, even though a very careful listener may find the major third a bit wide for harmony.

If you'd like to dig into the controversy, one of the objections to the above was voiced by Walford Davies in 1926: he discounted the possibility that the fourth is "somehow mysteriously disqualified because it is not to be found between a fundamental tone and any of its natural harmonics; for (unfortunately for this theory) exactly the same is true of the minor third from the bass, and that has been accepted as a concord for many a long day." The problem with Davie's argument is that it neglects the phenomenon of coincident harmonics that characterize the intervals we call "consonant." That is, what matters is not whether a given interval is found between the fundamental and any of its harmonics; what matters is whether there is any potential for agreement among the harmonics of two different complex tones. Sound together two notes a fourth apart, and the 4th partial of the lower note coincides with the 3rd of the upper tone; that's the relationship we're interested in. Sound together two notes a minor third apart, and the agreement is found between the 6th partial of the lower and the 5th partial of the higher tone, pretty close to the limit of what is easily perceived. Acoustically, then, the 4th is stronger than the minor third, but it is simultaneously less stable in a world of triadic harmony because it so obviously is an inversion of the even stronger perfect fifth. Here we see how perception depends on both physics and culture: the physics forms the basis of the effect, but our familiarity with triadic harmony leads us to expect certain things, too.

The fourth is a special case because its "dissonance" depends on its context. If the fourth is formed with the bass, it seems unstable because of its relationship with the fifth, being an "upside down" fifth. A listener familiar with triadic harmony hears this as implying the second inversion of a triad, in which the bass is the dominant tone; it sounds like something else is about to happen; that something being the arrival of a triad whose root is the bass. But if the fourth is not formed with the bass; if it's part of the upper voices in a triadic harmony, then it's perfectly stable because it's just like the fourth that appears between the 3rd and 4rd partials; the chord in that case is mimicking the basic stability of a single tone. Hence the counterpoint rule: a fourth is dissonant if formed with the bass.